# -*- coding: utf-8 -*-
# Part of Odoo. See LICENSE file for full copyright and licensing details.

from odoo import api, fields, models, _

import datetime
from dateutil.relativedelta import relativedelta

import logging
_logger = logging.getLogger(__name__)

class AccountBalance(models.Model):
    _inherit = "account.balance"
    
    @api.model
    @api.returns('self', lambda value: value.id)
    def find_open(self, date=None):
        rec = self.search([('is_init','=',True)],limit=1)
        if not rec:
            date0 = date and datetime.datetime.strptime(date,'%Y-%m-%d').date() or datetime.date.today()
            this_month_1st_day = datetime.date(date0.year, date0.month,1)
            #last_month_1st_day = this_month_1st_day + relativedelta(months=-1)
            rec = self.create({
                'date': this_month_1st_day,
                'is_init': True,
            })
            
        return rec

    @api.model
    @api.returns('self', lambda value: value.id)
    def generate_open(self, date):
        if isinstance( date, str ):
            date0 = datetime.datetime.strptime(date,'%Y-%m-%d').date()
        else:
            date0 = date
        
        this_month_1st_day = datetime.date(date0.year, date0.month,1)
        last_month_1st_day = this_month_1st_day - relativedelta(months=1)
        #next_month_1st_day = this_month_1st_day + relativedelta(months=1)

        rec_init = self.search([('date','=',this_month_1st_day),('is_init','=',True)])

        rec = self.search([('date','=',this_month_1st_day),('is_init','=',False)])
        rec = rec and rec or self.create({ 'date': this_month_1st_day })
        
        def fn_del_old(rec):
            rec.line_ids.filtered(lambda line: line.type == 'open').unlink()
        
        if rec_init:
            fn_del_old(rec)
            return rec

        fn_del_old(rec)

        query = """
WITH args1 as (
    SELECT %s company_id, %s date
--    SELECT 1 company_id, '2019-5-5'::date date

), args as (
    SELECT company_id, 
    date_trunc('month',date)::date as this_month,
    (date_trunc('month',date) + interval '1 month')::date as next_month,
    (date_trunc('month',date) - interval '1 month')::date as last_month,
    (date_trunc('month',date) + interval '1 year' + interval '1 month')::date as next_year_next_month
    FROM args1

), bal as (
    SELECT 
    account_id, sub_account_id, date_maturity,
    sum(debit) debit,
    sum(credit) credit 
    FROM account_balance_line abl
    JOIN account_balance ab
    ON abl.balance_id = ab.id
        and ab.company_id = (select company_id FROM args)
        AND date >= (select this_month FROM args)
        AND date < (select next_month FROM args)


    group by account_id, sub_account_id, date_maturity
    order by 1,2,3

)

select * from bal
        
        """
        self.env.cr.execute(query,( self.env.user.company_id.id, last_month_1st_day ) )
        query_results = self.env.cr.dictfetchall()
        
        def fn(vals):
            vals['type'] = 'open'
            return vals
        
        rec.write({
            'line_ids': [ (0,0, fn(vals) ) for vals in query_results ]
        })
        
        return rec

    @api.model
    @api.returns('self', lambda value: value.id)
    def generate_balance(self, date):
        date0 = datetime.datetime.strptime(date,'%Y-%m-%d').date()
        this_month_1st_day = datetime.date(date0.year, date0.month,1)
        #next_month_1st_day = this_month_1st_day + relativedelta(months=1)

        rec = self.generate_open( this_month_1st_day )

        query = """
WITH args1 as (
    SELECT %s company_id, %s date
--    SELECT 1 company_id, '2019-5-5'::date date

), args as (
    SELECT company_id, 
    date_trunc('month',date)::date as this_month,
    (date_trunc('month',date) + interval '1 month')::date as next_month,
    (date_trunc('month',date) - interval '1 month')::date as last_month,
    (date_trunc('month',date) + interval '1 year' + interval '1 month')::date as next_year_next_month
    FROM args1
), aa AS (
    Select aaat1.account_account_tag_id  account_tag_id, aa1.id account_id
    From account_account_account_tag aaat1, account_account aa1
    Where aaat1.account_account_id = aa1.id and aa1.company_id = (select company_id FROM args)

), aat AS (
    SELECT  name,  code, group_num, code4, sign, is_age, gt_one_year,aa.account_id
	FROM public.account_account_tag aat1
    LEFT JOIN aa on aat1.id = aa.account_tag_id
    WHERE aat1.type = 'balance'
    
    order by code
), account_one_year as (
    SELECT account_id FROM aat
    WHERE gt_one_year = 1 or is_age =1

), aml as (
    SELECT aml.account_id, sub_account_id,  
        --CASE WHEN a1y.account_id is null THEN 0
        --ELSE ( EXTRACT(YEAR from age( date_maturity, date_month_next ) ) * 12 + EXTRACT(MONTH from age( date_maturity, date_month_next )))
        --END::int month_age,
        --CASE WHEN a1y.account_id is null THEN 0
        --    WHEN date_maturity < date_after_a_year THEN 0
        --    ELSE 1 end gt_one_year

        CASE WHEN a1y.account_id is null THEN null
            ELSE date_maturity END date_maturity,
        debit,
        credit
    
    FROM account_move_line aml  
    LEFT JOIN account_account aa on aml.account_id = aa.id
    LEFT JOIN account_one_year a1y on aml.account_id = a1y.account_id
    LEFT JOIN account_move move ON aml.move_id = move.id
    WHERE move.state = 'posted'
        AND move.date >= (select this_month FROM args)
        AND move.date < (select next_month FROM args)
        AND move.company_id = (select company_id FROM args)
    
), ret as (
    SELECT account_id, sub_account_id, date_maturity,
    sum(debit) debit,
    sum(credit) credit
    from aml
    GROUP by account_id, sub_account_id, date_maturity
    ORDER BY 1,2,3
)

SELECT * from ret


        """
                      
        self.env.cr.execute(query,( self.env.user.company_id.id, this_month_1st_day ) )
        query_results = self.env.cr.dictfetchall()
        
        #rec = self.search([ ('date','=',this_month_1st_day)])
        #if not rec:
        #    rec = self.create({ 'date': this_month_1st_day })
        
        rec.line_ids.filtered(lambda line: line.type == 'delta').unlink()
        
        rec.write({
            'line_ids': [ (0,0, vals) for vals in query_results ]
        })
        
        return rec

    @api.model
    def get_balance_sheet(self, date):
        date0 = datetime.datetime.strptime(date,'%Y-%m-%d').date()
        this_month_1st_day = datetime.date(date0.year, date0.month,1)
        
        query = """
WITH args1 as (
    SELECT %s company_id, %s date
--    SELECT 1 company_id, '2019-5-5'::date date
), args as (
    SELECT company_id, 
    date_trunc('month',date)::date as this_month,
    date_trunc('year',date)::date as this_year,
    (date_trunc('month',date) + interval '1 month')::date as next_month,
    (date_trunc('month',date) - interval '1 month')::date as last_month,
    (date_trunc('month',date) + interval '1 year' + interval '1 month')::date as next_year_next_month,
    (date_trunc('year',date) + interval '1 year' )::date as next_year
    FROM args1
), aa AS (
    Select aaat1.account_account_tag_id  account_tag_id, aa1.id account_id
    From account_account_account_tag aaat1, account_account aa1
    Where aaat1.account_account_id = aa1.id and aa1.company_id = (select company_id FROM args)
), aat2 AS (
    SELECT  name,  code, group_num, code4, sign, gt_one_year
      , aa.account_id
	FROM public.account_account_tag aat1
    LEFT JOIN aa on aat1.id = aa.account_tag_id
    WHERE aat1.type = 'balance'
    order by code, account_id
), account_one_year as (
    SELECT account_id FROM aat2
    WHERE gt_one_year = 1
), aat as (
    SELECT * from aat2
    UNION SELECT '流动资产合计' as name, '14,1499' code, 14 group_num, '1499' code4, 1 sign , gt_one_year, account_id
         From aat2 Where group_num < 15
    UNION SELECT '非流动资产合计' as name, '19,1998' code, 19 group_num, '1998' code4, 1 sign, gt_one_year, account_id
         From aat2 Where group_num >= 15 and group_num < 20
    UNION SELECT '资产总计' as name, '19,1999' code, 19 group_num, '1999' code4, 1 sign, gt_one_year, account_id 
         From aat2 Where group_num < 20
    UNION SELECT '流动负债合计' as name, '24,2499' code, 24 group_num, '2499' code4, -1 sign, gt_one_year, account_id
         From aat2 Where group_num >= 20 and group_num < 25
    UNION SELECT '非流动负债合计' as name, '29,2998' code, 29 group_num, '2998' code4, -1 sign, gt_one_year, account_id
         From aat2 Where group_num >= 25 and group_num < 30
    UNION SELECT '负债合计' as name, '29,2999' code, 29 group_num, '2999' code4, -1 sign, gt_one_year, account_id 
         From aat2 Where group_num >= 20 and group_num < 30
    UNION SELECT '权益合计' as name, '49,4998' code, 49 group_num, '4998' code4, -1 sign, gt_one_year, account_id 
         From aat2 Where group_num >= 40 and group_num < 50
    UNION SELECT '负债和权益总计' as name, '49,4999' code, 49 group_num, '4999' code4, -1 sign, gt_one_year, account_id 
         From aat2 Where group_num >= 20 and group_num < 50
    order by code, account_id, gt_one_year
), abl AS (
    Select abl1.account_id, 
      CASE WHEN a1y.account_id is null THEN 0
          WHEN date_maturity <  (select next_year_next_month FROM args) THEN 0
          ELSE 1 end gt_one_year,
    
      CASE WHEN a1y.account_id is null THEN 0
          WHEN date_maturity <  (select next_year FROM args) THEN 0
          ELSE 1 end gt_one_year_for_year,
      type,
      balance,
      date_trunc('month',ab1.date)::date date,
      --a1y.account_id,
      --abl1.date_maturity, 
      --CASE WHEN a1y.account_id is null THEN null
      --      ELSE date_maturity END date_maturity,
      ab1.company_id
    From account_balance_line abl1
    JOIN account_balance ab1 
    ON abl1.balance_id = ab1.id and ab1.company_id = (select company_id FROM args)
    LEFT JOIN account_one_year a1y on abl1.account_id = a1y.account_id
    order by date, abl1.account_id
), abl_month AS (
    Select account_id, gt_one_year, type, balance, date
    From abl Where date = (select this_month FROM args)
), abl_year AS (
    Select account_id, gt_one_year_for_year gt_one_year, type, balance, date
    From abl 
    Where date = (select this_year FROM args)
      and type = 'open'
), aat_bal_month as (
    SELECT aat.name, 
       aat.code, 
       sum(COALESCE(abl_month.balance, 0)) balance
	FROM aat
    JOIN abl_month on aat.account_id = abl_month.account_id and aat.gt_one_year = abl_month.gt_one_year
    GROUP BY aat.name,  aat.code
    order by code
), aat_bal_year as (
    SELECT aat.name, 
       aat.code, 
       sum(COALESCE(abl_year.balance , 0)) year_open
	FROM aat
    JOIN abl_year on aat.account_id = abl_year.account_id and aat.gt_one_year = abl_year.gt_one_year
    GROUP BY aat.name,  aat.code
    order by code
), aat_all as (
    SELECT name, 
       (rank() over(  order by code ))::int as line_num,
       code, group_num, code4,sign
    FROM aat
    GROUP by name,code, group_num, code4,sign
    ORDER BY code
), aat_bal as (
    SELECT aat.name, 
       line_num,
       aat.code, 
       aat.group_num, aat.code4,aat.sign,
       COALESCE(aat_bal_month.balance, 0)  balance,
       COALESCE(aat_bal_year.year_open, 0) year_open
	FROM aat_all aat
    LEFT JOIN aat_bal_month on aat.code = aat_bal_month.code
    LEFT JOIN aat_bal_year on aat.code = aat_bal_year.code
), ret as (
    SELECT  * from aat_bal
    UNION SELECT '流动资产:'  as name, null line_num, '10,1000' code, 10 group_num, '1000' code4, 0 sign , null balance, null year_open 
    UNION SELECT '非流动资产:' as name, null line_num, '15,1500' code, 15 group_num, '1500' code4, 0 sign , null balance, null year_open 
    UNION SELECT '流动负债:'  as name, null line_num, '20,2000' code, 20 group_num, '2000' code4, 0 sign , null balance, null year_open 
    UNION SELECT '非流动负债:' as name, null line_num, '25,2500' code, 25 group_num, '2500' code4, 0 sign , null balance, null year_open 
    UNION SELECT '权益:' as name, null line_num, '40,4000' code, 40 group_num, '4000' code4, 0 sign , null balance, null year_open
    ORDER BY code
)

select (rank() over ( order by code))::int id,
    case when group_num = 12 then '其中：'
         when group_num = 16 then '减：'
    else '' end name0,
    name, line_num, sign, balance, year_open, code, group_num, code4 
from ret
    ORDER BY code;




        """
        
        self.env.cr.execute(query,( self.env.user.company_id.id, this_month_1st_day ))
        query_results = self.env.cr.dictfetchall()
        
        for index, val in enumerate( query_results):
            val['id'] = index
        
        """
        {
         'name0': u'',  #前缀 
         'name': u'货币资金', 
         'line_num': 1, 
         'sign': 1, 
         'balance': 22107.0,
         'year_open': 0.0, 
         'code': u'11,1000', 
         'code4': u'1000', 
         'group_num': 11, 
         }
        
        """
        
        return query_results

    @api.model
    def get_detail_ledger(self, date):
        date0 = datetime.datetime.strptime(date,'%Y-%m-%d').date()
        this_month_1st_day = datetime.date(date0.year, date0.month,1)
        
        query = """
WITH args1 as (
    SELECT %s company_id, %s date
--    SELECT 1 company_id, '2019-5-8'::date date

), args as (
    SELECT company_id, 
    date_trunc('month',date)::date as this_month,
    (date_trunc('month',date) + interval '1 month')::date as next_month,
    date_trunc('year',date)::date as this_year,
    (date_trunc('year',date) + interval '1 year')::date as next_year
    FROM args1
), aml as (
    SELECT account_id, 
    COALESCE(sub_account_id,0) sub_account_id,
    1 seq,
    rank() over( 
        partition by 
            account_id,
            sub_account_id
        order by aml.date, aml.id )
    as id,
    aml.date,
    am.name_cn move_name,
    aml.name as label,
    debit,
    credit,
    0 balance
    
    FROM account_move_line aml, account_move am
    WHERE aml.move_id = am.id 
    and am.company_id = (select company_id FROM args)
    and am.date >= (select this_month FROM args)
    and am.date < (select next_month FROM args)
), abl_all AS (
    Select account_id, COALESCE(sub_account_id,0) sub_account_id,
        type, 
        debit , 
        credit , 
        ab1.date, ab1.company_id
    From account_balance_line abl1, account_balance ab1
    Where abl1.balance_id = ab1.id and ab1.company_id = (select company_id FROM args)
    --order by 1,2,date

), abl AS (
    Select account_id, sub_account_id,
          date,
          sum( case when type='open' then debit else 0.00 end
             - case when type='open' then credit else 0.00 end ) balance_open, 
          sum( debit - credit ) balance_close, 
          sum( case when type='delta' then debit else 0.00 end ) debit,
          sum( case when type='delta' then credit else 0.00 end ) credit
    FROM abl_all Where date = (select this_month FROM args)
    GROUP BY account_id, sub_account_id, date
    order by 1,2
), abl_year AS (
    Select account_id, sub_account_id, 
         (date_trunc('month',max(date)+ interval '1 month') - interval '1 day')::date as date, 
        sum( case when type='delta' then debit else 0.00 end ) debit,
        sum( case when type='delta' then credit else 0.00 end ) credit
    FROM abl_all
    Where date >= (select this_year FROM args) and date < (select next_month FROM args)
      and account_id in (
          select account_id from abl_all
          where date = (select this_month FROM args)
      )
    GROUP BY account_id, sub_account_id
), balance_open AS (
    Select account_id, sub_account_id, 
        1 seq, 0 as rank, date, ''::varchar as name, '期初余额'::varchar as label, 
        0.00 debit, 0.00 credit, balance_open balance 
    FROM abl
), balance_close AS (
    Select account_id, sub_account_id,
        100 seq, 0 as rank, date, '' as name, '本期合计' as label, 
        debit, credit, balance_close balance 
    FROM abl
), balance_year AS (
    Select abl_year.account_id, 
          abl_year.sub_account_id,
    
        200 seq, 0 as rank, abl_year.date, '' as name, '本年累计' as label, 
        abl_year.debit, abl_year.credit, abl.balance_close balance
    FROM abl_year, abl 
    WHERE abl_year.account_id = abl.account_id and abl_year.sub_account_id = abl.sub_account_id

), line0 AS (
    Select * from balance_open
    UNION Select * from aml
), line AS (
    WITH RECURSIVE rec as (
      Select account_id, sub_account_id, 
          seq, rank, date, name, label, 
          debit, credit, balance
          from line0  where rank = 0
      UNION SELECT ch.account_id, ch.sub_account_id, 
          ch.seq, ch.rank, ch.date, ch.name, ch.label, 
          ch.debit, ch.credit, rec.balance + ch.debit - ch.credit
        from line0 ch, rec
        where ch.rank-1 = rec.rank
        and ch.account_id = rec.account_id
        and COALESCE(ch.sub_account_id,0) = COALESCE(rec.sub_account_id,0)
    )
    
    select * from rec
    --order by 1,2,3,4
    
), ret0 AS (
    Select * from line
    UNION Select * from balance_close
    UNION Select * from balance_year
    --order by 1,2,3,4
), ret AS (
    Select r.account_id, aa.code account_code, aa.name account_name,
    sub_account_id, asa.code sub_account_code, asa.name sub_account_name,
    seq,rank,date, r.name, label,debit,credit,balance
    from ret0 r
    LEFT JOIN account_account aa on r.account_id = aa.id
    LEFT JOIN account_sub_account asa on r.sub_account_id = asa.id
)

SELECT  (rank() over ( order by account_id, seq,rank))::int id, * FROM ret
order by account_id, sub_account_id, seq,rank





        """
        
        self.env.cr.execute(query,( self.env.user.company_id.id, this_month_1st_day ))
        query_results = self.env.cr.dictfetchall()

        for index, val in enumerate( query_results):
            val['id'] = index
        
        """
        {
        'account_id': 549, 
        'account_code': u'220300', 
        'account_name': u'预收账款', 
        'sub_account_id': 2, 
        'sub_account_code': u'00', 
        'sub_account_name': u'某供应商', 
        'date': u'2019-04-18', 
        'name': u'BNK1/2019/0001'
        'label': u'23336666', 
        'debit': 0.0, 
        'credit': 22220.0, 
        'balance': -22220.0, 
        'seq': 1,   # seq=1,rank=0, 期初余额
                     # seq=100,rank=0, 本期合计
                     # seq=200,rank=0, 本年累计
        'rank': 1,  # seq=1,rank>=1, 流水
        }
        """
        
        return query_results

    @api.model
    def get_general_ledger(self, date):
        date0 = datetime.datetime.strptime(date,'%Y-%m-%d').date()
        this_month_1st_day = datetime.date(date0.year, date0.month,1)
        
        query = """
WITH args1 as (
    SELECT %s company_id, %s date
--    SELECT 1 company_id, '2019-5-3'::date date

), args as (
    SELECT company_id, 
    date_trunc('month',date)::date as this_month,
    (date_trunc('month',date) + interval '1 month')::date as next_month,
    date_trunc('year',date)::date as this_year,
    (date_trunc('year',date) + interval '1 year')::date as next_year
    FROM args1
), aml as (
    SELECT 
    account_id, sub_account_id, aa.general_code,aa.general_name,
    1 seq,
    rank() over( 
        partition by 
            aa.general_code
        order by aml.date, aml.id )
    as id,
    aml.date,
    am.name_cn move_name,
    aml.name as label,
    debit,
    credit,
    0 balance
    
    FROM account_move_line aml, account_move am, account_account aa
    WHERE aml.move_id = am.id and aa.id = aml.account_id
    and am.company_id = (select company_id FROM args)
    and am.date >= (select this_month FROM args)
    and am.date < (select next_month FROM args)


), abl_all AS (
    Select account_id, 
    aa.general_code, aa.general_name, type,
        debit, credit, ab1.date, ab1.company_id
    From account_balance_line abl1, account_balance ab1, account_account aa
    Where abl1.balance_id = ab1.id and aa.id = abl1.account_id
    and ab1.company_id = (select company_id FROM args)

), abl AS (
    Select general_code, general_name,
          date, 
          sum( case when type='open' then debit else 0.00 end
             - case when type='open' then credit else 0.00 end ) balance_open, 
          sum( debit - credit ) balance_close, 
          sum( case when type='delta' then debit else 0.00 end ) debit,
          sum( case when type='delta' then credit else 0.00 end ) credit
    FROM abl_all Where date = (select this_month FROM args)
    GROUP BY general_code, general_name, date

), abl_year AS (
    Select general_code, general_name, 
          (date_trunc('month',max(date)+ interval '1 month') - interval '1 day')::date as date, 
        sum( case when type='delta' then debit else 0.00 end ) debit,
        sum( case when type='delta' then credit else 0.00 end ) credit
    FROM abl_all
    Where date >= (select this_year FROM args) and date < (select next_month FROM args)
      and account_id in (
          select account_id from abl_all
          where date = (select this_month FROM args)
      )
    GROUP BY general_code, general_name 
), balance_open AS (
    Select null::integer account_id, null::integer sub_account_id, general_code, general_name, 
        1 seq, 0 as rank, date, ''::varchar as name, '期初余额'::varchar as label, 
        0.00 debit, 0.00 credit, balance_open balance 
    FROM abl
), balance_close AS (
    Select null::integer account_id, null::integer sub_account_id, general_code, general_name, 
        100 seq, 0 as rank, date, ''::varchar as name, '本期合计' as label, 
        debit, credit, balance_close balance 
    FROM abl
), balance_year AS (
    Select null::integer account_id, null::integer sub_account_id, abl_year.general_code, 
          abl_year.general_name,
    
        200 seq, 0 as rank, abl_year.date, '' as name, '本年累计' as label, 
        abl_year.debit, abl_year.credit, abl.balance_close balance
    FROM abl_year, abl WHERE abl_year.general_code = abl.general_code
), line0 AS (
    Select * from balance_open
    UNION 
    Select * from aml
    order by general_code, seq,rank
), line AS (
    WITH RECURSIVE rec as (
      Select account_id, sub_account_id, general_code, general_name, 
          seq, rank, date, name, label, 
          debit, credit, balance
          from line0  where rank = 0
      UNION SELECT ch.account_id, ch.sub_account_id, ch.general_code, ch.general_name, 
          ch.seq, ch.rank, ch.date, ch.name, ch.label, 
          ch.debit, ch.credit, rec.balance + ch.debit - ch.credit
        from line0 ch, rec
        where ch.rank-1 = rec.rank
        and ch.general_code = rec.general_code
    )
    
    select * from rec
), ret AS (
    Select * from line

    UNION Select * from balance_close
    UNION Select * from balance_year

)

select (rank() over ( order by general_code, seq,rank ))::int id,* from ret
    order by general_code, seq,rank 





        """
        
        self.env.cr.execute(query,( self.env.user.company_id.id, this_month_1st_day ))
        query_results = self.env.cr.dictfetchall()
        
        for index, val in enumerate( query_results):
            val['id'] = index

        """
        {
        'account_id': 586, 
        'sub_account_id': 3, 
        'general_code': u'1002', 
        'general_name': u'银行', 
        'date': u'2019-04-18',
        'name': u'BNK1/2019/0001',
        'label': u'摘要', 
        'debit': 22220.0, 
        'credit': 0.0, 
        'balance': 22220.0, 
        'seq': 1,   # seq=1,rank=0, 期初余额
                     # seq=100,rank=0, 本期合计
                     # seq=200,rank=0, 本年累计
        'rank': 1,  # seq=1,rank>=1, 流水
        }
        """

        
        
        return query_results

    @api.model
    def get_profit_sheet(self, date):
        date0 = datetime.datetime.strptime(date,'%Y-%m-%d').date()
        this_month_1st_day = datetime.date(date0.year, date0.month,1)
        
        query = """
WITH args1 as (
    SELECT %s company_id, %s date
--    SELECT 1 company_id, '2019-5-8'::date date
), args_journal_id as (
    SELECT id as journal_id
	FROM public.account_journal
    where internal_code = 'profit'
        and type = 'profit'
        and company_id = (select company_id FROM args1)
    limit 1    
    
), args as (
    SELECT company_id, journal_id,
    date_trunc('month',date)::date as this_month,
    (date_trunc('month',date) + interval '1 month')::date as next_month,
    date_trunc('year',date)::date as this_year,
    (date_trunc('year',date) + interval '1 year')::date as next_year
    FROM args1, args_journal_id
), aa AS (
    Select aaat1.account_account_tag_id  account_tag_id, aa1.id account_id
    From account_account_account_tag aaat1, account_account aa1
    Where aaat1.account_account_id = aa1.id and aa1.company_id = (select company_id FROM args)
), aat_base AS (
    SELECT  name,  code, group_num, code4, sign, aa.account_id
	FROM public.account_account_tag aat1
    LEFT JOIN aa on aat1.id = aa.account_tag_id
    WHERE aat1.type = 'profit'
    
), aml_year as (
    SELECT account_id, am.date, balance
    FROM account_move_line aml, account_move am,account_account aa
    WHERE aml.move_id = am.id
    and aml.account_id = aa.id
    --and aa.internal_group in ('income','expense') 
    and am.journal_id = (select journal_id FROM args)
    and am.company_id = (select company_id FROM args)
    and am.date >= (select this_year FROM args)
    and am.date < (select next_month FROM args)
), aml as (
    SELECT account_id, balance
    FROM aml_year
    WHERE date >= (select this_month FROM args)
      and date < (select next_month FROM args)
), aat_aml_year as (
    Select name, code, group_num, code4, sign, -sum( COALESCE(balance, 0)) balance 
    from aat_base 
    LEFT JOIN aml_year on aat_base.account_id = aml_year.account_id
    GROUP BY name, code, group_num, code4, sign

), aat_aml_month as (
    Select name, code, group_num, code4, sign, -sum( COALESCE(balance, 0)) balance 
    from aat_base 
    LEFT JOIN aml on aat_base.account_id = aml.account_id
    GROUP BY name, code, group_num, code4, sign
    
), aat_aml as (
    Select tm.name, tm.code, tm.group_num, tm.code4, tm.sign, 
    tm.balance as amount_month, ty.balance as amount_year 
    from aat_aml_month tm, aat_aml_year ty
    WHERE tm.code = ty.code

), aat_20 as (
    SELECT '营业利润' as name, '20,6200' code, 20 group_num, '6200' code4, -1 sign, 
        sum(amount_month) amount_month, sum(amount_year) amount_year
    from aat_aml
    WHERE group_num < 20
), aat_30 as (
    SELECT '利润总额' as name, '30,6800' code, 30 group_num, '6800' code4, -1 sign, 
        sum(amount_month) amount_month, sum(amount_year) amount_year  
    from aat_aml
    WHERE group_num < 30
), aat_40 as (
    SELECT '净利润' as name, '40,6900' code, 40 group_num, '6900' code4, -1 sign, 
        sum(amount_month) amount_month, sum(amount_year) amount_year
    from aat_aml
), aat_all as (
    SELECT * from aat_aml
    UNION SELECT * from aat_20
    UNION SELECT * from aat_30
    UNION SELECT * from aat_40
), ret as (
    SELECT
    (case when group_num=10 then '一、'
       when group_num=20 then '二、'
       when group_num=30 then '三、'
       when group_num=40 then '四、'
       when sign = 1 then '减:'
       when sign = -1 then '加:'
       else ''
       end)::varchar name0,
    name,
    rank() over(  order by code ) as line_num,
    sign,
    amount_month,
    amount_year,
    code,group_num,code4
    from aat_all order by code        
)

select (rank() over ( order by code))::int id, * from ret;


        """
        self.env.cr.execute(query,( self.env.user.company_id.id, this_month_1st_day ))
        query_results = self.env.cr.dictfetchall()
        
        for index, val in enumerate( query_results):
            val['id'] = index

        """
        {
        'name0': u'减:',     #  前缀, 一、营业收入; 二、营业利润; 减: 营业成本
        'name': u'营业税金及附加', 
        'line_num': 3, 
        'sign': 1, 
        'amount_year': 0.0, 
        'amount_month': 0.0, 
        'code': u'11,6403', 
        'group_num': 11, 
        'code4': u'6403', 
        }
        """
        
        return query_results


    @api.model
    def get_balance(self, date):
        date0 = datetime.datetime.strptime(date,'%Y-%m-%d').date()
        this_month_1st_day = datetime.date(date0.year, date0.month,1)
        
        query = """
WITH args1 as (
    SELECT %s company_id, %s date
--    SELECT 1 company_id, '2019-5-3'::date date

), args as (
    SELECT company_id, 
    date_trunc('month',date)::date as this_month,
    (date_trunc('month',date) + interval '1 month')::date as next_month,
    date_trunc('year',date)::date as this_year,
    (date_trunc('year',date) + interval '1 year')::date as next_year
    FROM args1

), abl_all AS (
    Select account_id, sub_account_id,
        type,
        debit, credit, 
        ab1.date, ab1.company_id
    From account_balance_line abl1, account_balance ab1
    Where abl1.balance_id = ab1.id and ab1.company_id = (select company_id FROM args)

), abl_month AS (
    Select account_id, sub_account_id,
          date, 
          --sum( case when type='open' then debit else 0.00 end ) debit_open,
          --sum( case when type='open' then credit else 0.00 end ) credit_open, 
          sum( case when type='open' then debit else 0.00 end
             - case when type='open' then credit else 0.00 end ) balance_open, 

          sum( case when type='delta' then debit else 0.00 end ) debit,
          sum( case when type='delta' then credit else 0.00 end ) credit,
          
          sum( debit - credit ) balance_close 
          --sum(debit_close) debit_close, 
          --sum(credit_close) credit_close 
    FROM abl_all Where date = (select this_month FROM args)
    GROUP BY account_id, sub_account_id, date

), abl_year AS (
    Select account_id, sub_account_id, 
          (date_trunc('month',max(date)+ interval '1 month') - interval '1 day')::date as date, 
        sum( case when type='delta' then debit else 0.00 end ) debit,
        sum( case when type='delta' then credit else 0.00 end ) credit
        --sum(debit) debit, sum(credit) credit
    FROM abl_all
    Where date >= (select this_year FROM args) and date < (select next_month FROM args)
      and account_id in (
          select account_id from abl_all
          where date = (select this_month FROM args)
      )
    GROUP BY account_id, sub_account_id
    
    
), abl as (
    Select tm.account_id, tm.sub_account_id, tm.date, 
        case when tm.balance_open > 0 then tm.balance_open else 0.00 end debit_open, 
        case when tm.balance_open < 0 then -tm.balance_open else 0.00 end credit_open,
        tm.debit, tm.credit, 
        ty.debit debit_year, ty.credit credit_year, 
        
        case when tm.balance_close > 0 then tm.balance_close else 0.00 end debit_close, 
        case when tm.balance_close < 0 then -tm.balance_close else 0.00 end credit_close
    from abl_month tm, abl_year ty
    where tm.account_id  = ty.account_id
    and COALESCE( tm.sub_account_id, 0) = COALESCE( ty.sub_account_id, 0) 
    
), abl_general as (
    SELECT 1 as type, aa.general_code, aa.general_name, '' sub_code, '' sub_name,
        sum(debit_open) debit_open, 
        sum(credit_open) credit_open,
        sum(debit) debit, 
        sum(credit) credit, 
        sum(debit_year) debit_year, 
        sum(credit_year) credit_year, 
        sum(debit_close) debit_close, 
        sum(credit_close) credit_close
    FROM abl, account_account aa
    WHERE aa.id = abl.account_id
    GROUP BY aa.general_code, aa.general_name
), abl_detail as (
    SELECT 2 as type, aa.code, aa.name, asa.code sub_code, asa.name sub_name,
        debit_open, credit_open,
        debit, credit, 
        debit_year, credit_year, 
        debit_close, credit_close
    FROM abl 
    LEFT JOIN account_account aa on aa.id = abl.account_id
    LEFT JOIN account_sub_account asa on asa.id = abl.sub_account_id
     
), bal as (
    SELECT * FROM abl_detail
    UNION SELECT * FROM abl_general
    ORDER BY code
)

SELECT (rank() over ( order by code))::int id, * FROM bal

        
        """
        
        self.env.cr.execute(query,( self.env.user.company_id.id, this_month_1st_day))
        query_results = self.env.cr.dictfetchall()
        
        for index, val in enumerate( query_results):
            val['id'] = index

        """
        {
        'type': 1,        #1 总账科目, 2 明细账科目
        'code': '1002',   #总账科目代码
        'name': '银行存款', # 总账科目名称
        'sub_code': '00',  # 明细账科目代码
        'sub_name': '民生银行6226123456781234',  # 明细账科目名称
        'debit_open': 0,
        'credit_open': 0,
        'debit': 0,
        'credit': 0,
        'debit_year': 0,
        'credit_year': 0,
        'debit_close': 0,
        'credit_close': 0,
        }
        """
        
        return query_results

    @api.model
    def get_move_sum(self, date):
        date0 = datetime.datetime.strptime(date,'%Y-%m-%d').date()
        this_month_1st_day = datetime.date(date0.year, date0.month,1)
        next_month_1st_day = this_month_1st_day + relativedelta(months=1)
        
        domain = [
            ('date','>=', this_month_1st_day),
            ('date','<', next_month_1st_day),
            ('state','=','posted') ]
        
        moves = self.env['account.move'].search(domain)
        move_count = len(moves)
        attachment_count = sum( moves.mapped( 'attachment_count' ) )
        
            
        return {
            'company_id': self.env.user.company_id.id,
            'company_name': self.env.user.company_id.name,
            'date': this_month_1st_day,
            'move_count': move_count,
            'attachment_count': attachment_count,
        }


    @api.model
    def get_move_sum_lines(self, date):
        date0 = datetime.datetime.strptime(date,'%Y-%m-%d').date()
        this_month_1st_day = datetime.date(date0.year, date0.month,1)
        
        query = """
WITH args1 as (
    SELECT %s company_id, %s date
--    SELECT 1 company_id, '2019-5-3'::date date

), args as (
    SELECT company_id, 
    date_trunc('month',date)::date as this_month,
    (date_trunc('month',date) + interval '1 month')::date as next_month,
    date_trunc('year',date)::date as this_year,
    (date_trunc('year',date) + interval '1 year')::date as next_year
    FROM args1
), aml_all as (
    Select account_id, sub_account_id,
        debit, credit, 
        am1.date, am1.company_id
    From account_move_line aml1, account_move am1
    Where aml1.move_id = am1.id
    and am1.company_id = (select company_id FROM args)
    and state='posted'
    and am1.date >= (select this_month FROM args)
    and am1.date < (select next_month FROM args)
), aml as (
    Select aa.general_code code, aa.general_name as name, 
          sum(debit) debit, 
          sum(credit) credit
    FROM aml_all, account_account aa
    Where aa.id = aml_all.account_id
    GROUP BY aa.general_code, aa.general_name
)

SELECT (rank() over ( order by code))::int id, * FROM aml
ORDER BY code



        """
        
        self.env.cr.execute(query,( self.env.user.company_id.id, this_month_1st_day))
        query_results = self.env.cr.dictfetchall()
        
        for index, val in enumerate( query_results):
            val['id'] = index

        """
        {
        'code': '1002',   #总账科目代码
        'name': '银行存款', # 总账科目名称
        'debit': 0,
        'credit': 0,
        }
        """
        
        return query_results

